package ee.maoinvest.backend.model.dao.postgresqlimplementations;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import ee.maoinvest.backend.database.ConnectionManager;
import ee.maoinvest.backend.model.dao.EnterpriseDAO;
import ee.maoinvest.backend.model.data.Enterprise;

public class EnterpriseDAOPostgresqlImpl implements EnterpriseDAO
{
	Logger logger = Logger.getLogger(EnterpriseDAOPostgresqlImpl.class);
	Connection dbConnection;
	String sql;
	PreparedStatement preparedStatement;
	Statement statement;
	ResultSet resultSet;
	private final int WARNING_NO_ROWS_AFFECTED = 0;
	private int rowsAffected;
	
	public EnterpriseDAOPostgresqlImpl(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}
	
//-----------------START OF BASIC CRUD-----------------------------------

	@Override
	public int insertEnterprise(Enterprise enterprise)
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "INSERT INTO Enterprises " + 
				"(informalName, businessName, registryCode, dateInserted, dateUpdated, " +  
				"isActive, isWrittenOff, dateOfWriteOff, isDefaultEnterprise) " + 
				"VALUES " + 
				"(?, ?, ?, ?, ?, " +  
				"?, ?, ?, ?);";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			preparedStatement.setObject	(1, enterprise.getInformalName(), Types.VARCHAR);
			preparedStatement.setString	(2, enterprise.getBusinessName());
			preparedStatement.setObject	(3, enterprise.getRegistryCode(), Types.VARCHAR);
			preparedStatement.setDate	(4, enterprise.getDateInserted());
			preparedStatement.setObject	(5, enterprise.getDateUpdated(), Types.DATE);
			preparedStatement.setBoolean(6, enterprise.getIsActive());
			preparedStatement.setBoolean(7, enterprise.getIsWrittenOff());
			preparedStatement.setObject	(8, enterprise.getDateOfWriteOff(), Types.DATE);
			preparedStatement.setBoolean(9, enterprise.getIsDefaultEnterprise());
			
			logger.info(preparedStatement.toString());
			rowsAffected = preparedStatement.executeUpdate();
			
			if (rowsAffected == 1)
			{
				resultSet = preparedStatement.getGeneratedKeys();
				resultSet.next(); //answer consist only 1 row, so move the cursor just once to start to process the result
				int newId = resultSet.getInt(1); // 1, because identifiers are held in the first column 
				enterprise.setEnterpriseID(newId);
				return rowsAffected;
			}
			else
			{
				System.out.println("No rows affected!");
				logger.info("Unsuccessful insertion to a database: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public Enterprise getEnterpriseByID(int enterpriseID) 
	{
		resultSet = null;
		Enterprise enterpriseRetrieved = null;
		sql = "SELECT * FROM Enterprises WHERE enterpriseID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, enterpriseID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				enterpriseRetrieved = new Enterprise();
				enterpriseRetrieved.setEnterpriseID(resultSet.getInt("enterpriseID"));
				enterpriseRetrieved.setInformalName((String) resultSet.getObject("informalName"));
				enterpriseRetrieved.setBusinessName(resultSet.getString("businessName"));
				enterpriseRetrieved.setRegistryCode((String) resultSet.getObject("registryCode"));
				enterpriseRetrieved.setDateInserted(resultSet.getDate("dateInserted"));
				enterpriseRetrieved.setDateUpdated((Date) resultSet.getObject("dateUpdated"));
				enterpriseRetrieved.setIsActive(resultSet.getBoolean("isActive"));
				enterpriseRetrieved.setIsWrittenOff(resultSet.getBoolean("isWrittenOff"));
				enterpriseRetrieved.setDateOfWriteOff((Date) resultSet.getObject("dateOfWriteOff"));
				enterpriseRetrieved.setIsDefaultEnterprise(resultSet.getBoolean("isDefaultEnterprise"));
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return enterpriseRetrieved;
	}

	@Override
	public int updateEnterprise(Enterprise enterprise) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Enterprises " + 
				"SET informalName = ?, businessName = ?, registryCode = ?, dateInserted = ?, dateUpdated = ?, " +  
				"isActive = ?, isWrittenOff = ?, dateOfWriteOff = ?, isDefaultEnterprise = ? " + 
				"WHERE enterpriseID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setObject	(	1, enterprise.getInformalName(), Types.VARCHAR);
			preparedStatement.setString	(	2, enterprise.getBusinessName());
			preparedStatement.setObject	(	3, enterprise.getRegistryCode(), Types.VARCHAR);
			preparedStatement.setDate	(	4, enterprise.getDateInserted());
			preparedStatement.setObject	(	5, enterprise.getDateUpdated(), Types.DATE);
			preparedStatement.setBoolean(	6, enterprise.getIsActive());
			preparedStatement.setBoolean(	7, enterprise.getIsWrittenOff());
			preparedStatement.setObject	(	8, enterprise.getDateOfWriteOff(), Types.DATE);
			preparedStatement.setBoolean(	9, enterprise.getIsDefaultEnterprise());
			preparedStatement.setInt	(	10, enterprise.getEnterpriseID());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public int deleteEnterprise(int enterpriseID) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "DELETE FROM Enterprises WHERE enterpriseID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			preparedStatement.setInt(1, enterpriseID);
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Deletion successful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.info("Deletion unsuccessful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch(SQLException e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
//-----------------END OF BASIC CRUD-----------------------------------
	@Override
	public List<Enterprise> getAllEnterprisesThatAreNotWrittenOff() 
	{
		List<Enterprise> listOfEnterprises = new ArrayList<Enterprise>();
		Enterprise enterpriseRetrieved = null;
		sql = "SELECT * FROM Enterprises WHERE isWrittenOff = false;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				enterpriseRetrieved = fillEnterpriseObjectWithDataRetrievedFromDB();
				
				listOfEnterprises.add(enterpriseRetrieved);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfEnterprises;
	}
	

	@Override
	public Enterprise getDefaultEnterprise() 
	{
		Enterprise enterpriseRetrieved = null;
		sql = "SELECT * FROM Enterprises WHERE isDefaultEnterprise = true AND isWrittenOff = false;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				enterpriseRetrieved = fillEnterpriseObjectWithDataRetrievedFromDB();
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return enterpriseRetrieved;
	}
	
	@Override
	public int setIsEnterpriseActive(int enterpriseID, boolean isActive) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Enterprises " + 
				"SET isActive = ? " + 
				"WHERE enterpriseID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setBoolean(1, isActive);
			preparedStatement.setInt	(2, enterpriseID);
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
	@Override
	public Date getDateDefaultEnterpriseWasInsertedIntoSystem() 
	{
		Date dateRetrieved = null;
		sql = "SELECT dateInserted FROM Enterprises WHERE isDefaultEnterprise = true;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("dateInserted");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}
	
	//HELPER METHODS 
	private Enterprise fillEnterpriseObjectWithDataRetrievedFromDB()
	{
		Enterprise enterpriseRetrieved = null;
		
		try 
		{
			enterpriseRetrieved = new Enterprise();
			
			enterpriseRetrieved.setEnterpriseID(resultSet.getInt("enterpriseID"));
			enterpriseRetrieved.setInformalName((String) resultSet.getObject("informalName"));
			enterpriseRetrieved.setBusinessName(resultSet.getString("businessName"));
			enterpriseRetrieved.setRegistryCode((String) resultSet.getObject("registryCode"));
			enterpriseRetrieved.setDateInserted(resultSet.getDate("dateInserted"));
			enterpriseRetrieved.setDateUpdated((Date) resultSet.getObject("dateUpdated"));
			enterpriseRetrieved.setIsActive(resultSet.getBoolean("isActive"));
			enterpriseRetrieved.setIsWrittenOff(resultSet.getBoolean("isWrittenOff"));
			enterpriseRetrieved.setDateOfWriteOff((Date) resultSet.getObject("dateOfWriteOff"));
			enterpriseRetrieved.setIsDefaultEnterprise(resultSet.getBoolean("isDefaultEnterprise"));
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return enterpriseRetrieved;
	}	
}